{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. 处理Excel 电子表格笔记（第12章)  \n",
    "本文主要介绍openpyxl 的2.5.12版处理excel电子表格，原书是2.1.4 版，OpenPyXL 团队会经常发布新版本。不过不用担心，新版本应该在相当长的时间内向后兼容。如果你有新版本，想看看它提供了什么新功能，可以查看OpenPyXL 的完整文档：\n",
    "http://openpyxl.readthedocs.org/。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1.1 读取Excel文档**  \n",
    "常用函数如下：\n",
    "\n",
    "|函数(2.5.12)|函数(2.1.4)|用途|备注|\n",
    "|:----  |:----  | ----  | :----:  |\n",
    "| openpyxl.load_workbook('example.xlsx')| openpyxl.load_workbook('example.xlsx')|打开excel文档 | |\n",
    "| wb.sheetnames| wb.get_sheet_names()| 取得工作簿中所有表名的列表 ||\n",
    "|sheet = wb['Sheet']|sheet = wb.get_sheet_by_name('Sheet')|获得工作簿表格Sheet||\n",
    "|sheet.title|sheet.title|获得sheet的表格名||\n",
    "|anotherSheet = wb.active|anotherSheet = wb.get_active_sheet()|获得当前活动表|活动表就是工作簿在Excel 中打开时出现的工作表|\n",
    "|sheet['A1'].value|sheet['A1'].value|获得A1框格的值|可以通过=赋值改变A1框格的值|\n",
    "|sheet.cell(row=1, column=2)|sheet.cell(row=1, column=2)|获得B2框格的值|另一种赋值方法，row表示行号，row代表用数字标识的列号|\n",
    "|sheet.max_row/sheet.max_column|sheet.get_highest_row()/sheet.get_highest_column()|获得表格最大行数/列数|返回整数|\n",
    "|from openpyxl.utils import get_column_letter, column_index_from_string|from openpyxl.cell import get_column_letter, column_index_from_string|调用数字字母转换函数||\n",
    "|get_column_letter(2)|get_column_letter(2)|将数字2转为字母B||\n",
    "|column_index_from_string('AA')|column_index_from_string('AA')|将字母AA转为数字27||\n",
    "|tuple(sheet['A1':'C3'])|tuple(sheet['A1':'C3'])|获得A1到C3处的单元格||"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1.2 写入Excel文档**  \n",
    "常用函数如下：\n",
    "\n",
    "|函数(2.5.12)|函数(2.1.4)|用途|备注|\n",
    "|:----  |:----  | ----  | :----: |\n",
    "|wb = openpyxl.Workbook()|wb = openpyxl.Workbook() |创建新的工作表对象||\n",
    "|wb.create_sheet(index=2, title='Middle Sheet'))|wb.create_sheet(index=2, title='Middle Sheet'))|创建序号为index+1的新工作表|可以缺省输入参数|\n",
    "|wb.remove(wb['Sheet1'])|wb.remove_sheet(wb.get_sheet_by_name('Sheet'))|删除工作表Sheet1||\n",
    "|wb.save('example_copy.xlsx')|wb.save('example_copy.xlsx')|保存xlsx文件||"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**1.3 表格设置**  \n",
    "常用函数如下：\n",
    "\n",
    "|函数(2.5.12)|函数(2.1.4)|用途|备注|\n",
    "|:---- |:---- | ----  | :----: |\n",
    "|from openpyxl.styles import Font|from openpyxl.styles import Font, Style|导入字体设置模块|\n",
    "|italic24Font = Font(name='Times New Roman',size=24, italic=True,bold=True)<br>sheet['A1'].font = italic24Font|italic24Font = Font(name='Times New Roman',size=24, italic=True,bold=True)<br>styleObj = Style(font=italic24Font)<br> sheet['A1'].style=styleObj|设置A1单元格字体，斜体加粗，字号24||\n",
    "|sheet['A3'] = '=SUM(A1:A2)'|sheet['A3'] = '=SUM(A1:A2)'|设置公式|类似excel公式处理|\n",
    "|sheet.row_dimensions[1].height = 70|sheet.row_dimensions[1].height = 70|设置第1行高度||\n",
    "|sheet.column_dimensions['B'].width = 20|sheet.column_dimensions['B'].width = 20|设置第B列宽度||\n",
    "|sheet.merge_cells('A1:D3')|sheet.merge_cells('A1:D3')|将'A1:D3'12 个单元格合并为一个单元格||\n",
    "|sheet.unmerge_cells('C5:D5')|sheet.unmerge_cells('C5:D5')|解除单元格合并||\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. 项目练习"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 从电子表格中读取数据  \n",
    "2010 年美国人口普查数据censuspopdata.xlsx，可以从https://download.csdn.net/download/luohenyj/11266976 下载。程序主要功能：\n",
    "1. 读取电子表格数据\n",
    "2. 填充数据结构\n",
    "3. 将结果写入文件\n",
    "代码如下：\n",
    "\n",
    "```python\n",
    "import openpyxl\n",
    "import pprint\n",
    "\n",
    "\n",
    "print('Opening workbook...')\n",
    "# 打开excel文档\n",
    "wb = openpyxl.load_workbook('censuspopdata.xlsx')\n",
    "# 打开 工作表\n",
    "#sheet = wb.get_sheet_by_name('Population by Census Tract')\n",
    "sheet = wb['Population by Census Tract']\n",
    "countyData = {}\n",
    "print('Reading rows...')\n",
    "# 按行读取数据\n",
    "for row in range(2, sheet.max_row + 1):\n",
    "    state = sheet['B' + str(row)].value\n",
    "    county = sheet['C' + str(row)].value\n",
    "    pop = sheet['D' + str(row)].value\n",
    "\n",
    "    # Make sure the key for this state exists.\n",
    "    countyData.setdefault(state, {})\n",
    "\n",
    "    # country没有时设置的默认值\n",
    "    countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})\n",
    "    # Each row represents one census tract, so increment by one.\n",
    "    # 计算每个县中普查区的数\n",
    "    countyData[state][county]['tracts'] += 1\n",
    "\n",
    "    # Increase the county pop by the pop in this census tract.\n",
    "    # 统计总人口\n",
    "    countyData[state][county]['pop'] += int(pop)\n",
    "\n",
    "\n",
    "# Open a new text file and write the contents of countyData to it.\n",
    "print('Writing results...')\n",
    "resultFile = open('census2010.py', 'w')\n",
    "# 保存数据\n",
    "resultFile.write('allData = ' + pprint.pformat(countyData))\n",
    "resultFile.close()\n",
    "print('Done.')\n",
    "\n",
    "'''\n",
    "import os\n",
    "# 导入py数据文件\n",
    "import census2010\n",
    "# 打印某一项数据\n",
    "census2010.allData['AK']['Anchorage']\n",
    "anchoragePop = census2010.allData['AK']['Anchorage']['pop']\n",
    "print('The 2010 population of Anchorage was ' + str(anchoragePop))\n",
    "'''\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 更新一个电子表格  \n",
    "从produceSales.xlsx找到特定类型的产品，并更新它们的价格。produceSales.xlsx可以从https://download.csdn.net/download/luohenyj/11266976 下载"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "# 打开文档\n",
    "wb = openpyxl.load_workbook('produceSales.xlsx')\n",
    "#sheet = wb.get_sheet_by_name('Sheet')\n",
    "sheet = wb['Sheet']\n",
    "\n",
    "# 要改变的价格\n",
    "PRICE_UPDATES = {'Garlic': 3.07,\n",
    "                 'Celery': 1.19,\n",
    "                 'Lemon': 1.27}\n",
    "\n",
    "\n",
    "# skip the first row 跳过首行\n",
    "for rowNum in range(2, sheet.max_row):\n",
    "    # 获得该行第一列的值\n",
    "    produceName = sheet.cell(row=rowNum, column=1).value\n",
    "    # 判断键值\n",
    "    if produceName in PRICE_UPDATES:\n",
    "        # 更新值\n",
    "        sheet.cell(row=rowNum, column=2).value = PRICE_UPDATES[produceName]\n",
    "\n",
    "wb.save('updatedProduceSales.xlsx')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 乘法表  \n",
    "从命令行接受数字N，在一个Excel 电子表格中创建一个N×N 的乘法表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please input N:\n",
      "9\n"
     ]
    }
   ],
   "source": [
    "\n",
    "import openpyxl\n",
    "from openpyxl.utils import get_column_letter, column_index_from_string\n",
    "# 设置字体风格\n",
    "from openpyxl.styles import Font\n",
    "\n",
    "wb = openpyxl.Workbook()\n",
    "sheet = wb['Sheet']\n",
    "\n",
    "print(\"Please input N:\")\n",
    "N = int(input())\n",
    "A = get_column_letter(1)\n",
    "# 设置字体加粗\n",
    "fontObj = Font(bold=True)\n",
    "# 设置行\n",
    "for row in range(2, N+2):\n",
    "\n",
    "    sheet['A'+str(row)] = row-1\n",
    "    sheet['A'+str(row)].font = fontObj\n",
    "\n",
    "# 设置列\n",
    "for col in range(2, N+2):\n",
    "    colName = get_column_letter(col)\n",
    "    sheet[colName+str(1)] = col-1\n",
    "    sheet[colName+str(1)].font = fontObj\n",
    "\n",
    "\n",
    "for row in range(2, N+2):\n",
    "    for col in range(2, N+2):\n",
    "        colName = get_column_letter(col)\n",
    "        rowName = row\n",
    "        sheet[colName+str(row)] = sheet[colName+str(1)\n",
    "                                        ].value*sheet['A'+str(row)].value\n",
    "\n",
    "wb.save('multiplicationTable.xlsx')\n",
    "wb.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 空行插入程序  \n",
    "\n",
    "命令行参数输入N和M。从第N行开始，在电子表格中插入M 个空行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "空行起始行号：\n",
      "2\n",
      "空行数：\n",
      "3\n"
     ]
    }
   ],
   "source": [
    "import openpyxl\n",
    "\n",
    "\n",
    "xlsxPath='multiplicationTable.xlsx'\n",
    "# 打开文件\n",
    "wb = openpyxl.load_workbook(xlsxPath)\n",
    "\n",
    "sheet = wb['Sheet']\n",
    "print(\"空行起始行号：\")\n",
    "rowBlank=int(input())\n",
    "print(\"空行数：\")\n",
    "rowBlanInsert=int(input())\n",
    "# 新的文件\n",
    "wb_save = openpyxl.Workbook()\n",
    "sheet_save = wb_save['Sheet']\n",
    "\n",
    "\n",
    "# 保存空行的数据\n",
    "for row in range(1,sheet.max_row+1):\n",
    "    for col in range(1,sheet.max_column+1):\n",
    "        # 保存前rowBlank的值\n",
    "        if row<rowBlank:    \n",
    "            sheet_save.cell(row,col).value=sheet.cell(row,col).value\n",
    "        else:\n",
    "            sheet_save.cell(row+rowBlanInsert,col).value=sheet.cell(row,col).value\n",
    "\n",
    "       \n",
    "\n",
    "wb_save.save('blankRowInserter.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.5 电子表格单元格翻转程序  \n",
    "编写一个程序，翻转电子表格中行和列的单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "\n",
    "xlsxPath = 'blankRowInserter.xlsx'\n",
    "# 打开文件\n",
    "wb = openpyxl.load_workbook(xlsxPath)\n",
    "\n",
    "sheet = wb['Sheet']\n",
    "# 新的文件\n",
    "wb_save = openpyxl.Workbook()\n",
    "sheet_save = wb_save['Sheet']\n",
    "\n",
    "\n",
    "# 翻转数据\n",
    "for row in range(1, sheet.max_row+1):\n",
    "    for col in range(1, sheet.max_column+1):\n",
    "            sheet_save.cell(col, row).value = sheet.cell(row, col).value\n",
    "\n",
    "wb_save.save('transSheet.xlsx')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.6 文本文件到电子表格  \n",
    "读入几个文本文件的内容（可以自己创造这些文本文件），并将这些内容插入一个电子表格，每行写入一行文本。第一个文本文件中的行将写入\n",
    "列A 中的单元格，第二个文本文件中的行将写入列B 中的单元格，以此类推。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import openpyxl\n",
    "import os\n",
    "\n",
    "\n",
    "wb = openpyxl.Workbook()\n",
    "sheet = wb['Sheet']\n",
    "\n",
    "\n",
    "searchPath = './my_test'\n",
    "# 返回指定目录下所有的文件名和文件夹名列表\n",
    "fileNames = os.listdir(searchPath)\n",
    "txtNames = []\n",
    "\n",
    "for fileName in fileNames:\n",
    "    # 如果结尾是txt\n",
    "    if fileName.endswith('.txt'):\n",
    "        # 保存到列表\n",
    "        txtNames.append(fileName)\n",
    "\n",
    "\n",
    "# 遍历文件\n",
    "for i in range(len(txtNames)):\n",
    "    txtPath = os.path.join(searchPath, txtNames[i])\n",
    "    file = open(txtPath)\n",
    "    fileContents = file.readlines()\n",
    "    for j in range(len(fileContents)):\n",
    "        sheet.cell(i+1, j+1).value = fileContents[j]\n",
    "\n",
    "wb.save('txtToXlsx.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.7 电子表格到文本文件  \n",
    "将列A 中的单元格写入一个文本文件，将列B 中的单元格写入另一个文本文件，以此类推。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "\n",
    "\n",
    "import openpyxl\n",
    "import os\n",
    "\n",
    "\n",
    "wb = openpyxl.load_workbook('txtToXlsx.xlsx')\n",
    "sheet = wb.active\n",
    "\n",
    "for rows in range(sheet.max_row):\n",
    "    txtFile = open('%s.txt' % (rows), 'w')\n",
    "    # 遍历行\n",
    "    for cols in sheet[rows+1]:\n",
    "        txtFile.write(str(cols.value))\n",
    "    txtFile.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
